/*
 * MIT License
 *
 * Copyright (c) 2023 北京凯特伟业科技有限公司
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 */
package com.je.common.base.service.db.impl;

import com.google.common.base.Strings;
import com.je.common.base.DynaBean;
import com.je.common.base.entity.QueryInfo;
import com.je.core.entity.extjs.JSONTreeNode;
import com.je.common.base.service.db.PcDBMethodService;
import org.springframework.stereotype.Service;

import java.util.List;

@Service("pcDBMethod4OracleService")
public class PcDBMethod4OracleServiceImpl implements PcDBMethodService {

    /**
     * 构建查询同步树的sql语句
     *
     * @param template  TODO 暂不明确
     * @param queryInfo TODO 暂不明确
     * @param tableName 表名称
     * @param rootId    根节点id
     * @return
     */
    @Override
    public String getTreeSql(JSONTreeNode template, QueryInfo queryInfo, String tableName, String rootId) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT t." + template.getId()); //主键
        sql.append(", t." + template.getText()); // 名称
        sql.append(", t." + template.getCode()); // 编码
        sql.append(", t." + template.getParent()); // 父节点
        // 节点类型
        if (!Strings.isNullOrEmpty(template.getNodeType())) {
            sql.append(", t." + template.getNodeType());
        }
        // 节点信息
        if (!Strings.isNullOrEmpty(template.getNodeInfo())) {
            sql.append(", t." + template.getNodeInfo());
        }
        // 节点信息
        if (!Strings.isNullOrEmpty(template.getLayer())) {
            sql.append(", t." + template.getLayer());
        }
        // 节点信息类型
        if (!Strings.isNullOrEmpty(template.getNodeInfoType())) {
            sql.append(", t." + template.getNodeInfoType());
        }
        // 图标图片地址
        if (!Strings.isNullOrEmpty(template.getIcon())) {
            sql.append(", t." + template.getIcon());
        }
        // 图标颜色
        if (!Strings.isNullOrEmpty(template.getIconColor())) {
            sql.append(", t." + template.getIconColor());
        }
        //是否禁用
        if (!Strings.isNullOrEmpty(template.getDisabled())) {
            sql.append(", t." + template.getDisabled());
        }
        //树形路径
        if (!Strings.isNullOrEmpty(template.getNodePath())) {
            sql.append(", t." + template.getNodePath());
        }
        //描述
        if (!Strings.isNullOrEmpty(template.getDescription())) {
            sql.append(", t." + template.getDescription());
        }
        if (!Strings.isNullOrEmpty(template.getOrderIndex())) {
            sql.append(", t." + template.getOrderIndex());
        }
        if (!Strings.isNullOrEmpty(template.getTreeOrderIndex())) {
            sql.append(", t." + template.getTreeOrderIndex());
        }

        sql.append(" FROM " + tableName + " t ");
        sql.append(" where (1=1 ");
        if (null != queryInfo) {
            sql.append(queryInfo.getWhereSql());
        }
        sql.append(") OR " + template.getId() + " = '" + rootId + "' ");
        sql.append(" START WITH t." + template.getId() + " = '" + rootId + "'");
        sql.append(" CONNECT BY t." + template.getParent() + " = PRIOR " + template.getId() + " ");
        if (null != queryInfo && !Strings.isNullOrEmpty(queryInfo.getOrderSql())) {
            sql.append(queryInfo.getOrderSql());
        } else {
            sql.append(" ORDER BY " + template.getParent() + " ASC");
            if (!Strings.isNullOrEmpty(template.getOrderIndex())) {
                sql.append(", " + template.getOrderIndex() + " ASC");
            }
        }
        return sql.toString();
    }

    /**
     * 构建查询异步树的sql语句
     *
     * @param template     TODO 暂不明确
     * @param queryInfo    TODO 暂不明确
     * @param tableName    表名称
     * @param rootId       根节点id
     * @param isRoot       TODO 暂不明确
     * @param onlyWhereSql TODO 暂不明确
     * @return
     */
    @Override
    public String getAsynTreeSql(JSONTreeNode template, QueryInfo queryInfo,
                                 String tableName, String rootId, Boolean isRoot, Boolean onlyWhereSql) {
        // TODO Auto-generated method stub
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT t." + template.getId()); //主键
        sql.append(", t." + template.getText()); // 名称
        sql.append(", t." + template.getCode()); // 编码
        sql.append(", t." + template.getParent()); // 父节点
        // 节点类型
        if (!Strings.isNullOrEmpty(template.getNodeType())) {
            sql.append(", t." + template.getNodeType());
        }
        // 节点信息
        if (!Strings.isNullOrEmpty(template.getNodeInfo())) {
            sql.append(", t." + template.getNodeInfo());
        }
        // 节点信息类型
        if (!Strings.isNullOrEmpty(template.getNodeInfoType())) {
            sql.append(", t." + template.getNodeInfoType());
        }
        // 图标图片地址
        if (!Strings.isNullOrEmpty(template.getIcon())) {
            sql.append(", t." + template.getIcon());
        }
        // 图标样式
        if (!Strings.isNullOrEmpty(template.getIconColor())) {
            sql.append(", t." + template.getIconColor());
        }
        //是否禁用
        if (!Strings.isNullOrEmpty(template.getDisabled())) {
            sql.append(", t." + template.getDisabled());
        }
        //树形路径
        if (!Strings.isNullOrEmpty(template.getNodePath())) {
            sql.append(", t." + template.getNodePath());
        }
        //描述
        if (!Strings.isNullOrEmpty(template.getDescription())) {
            sql.append(", t." + template.getDescription());
        }
        if (!Strings.isNullOrEmpty(template.getOrderIndex())) {
            sql.append(", t." + template.getOrderIndex());
        }
        if (!Strings.isNullOrEmpty(template.getTreeOrderIndex())) {
            sql.append(", t." + template.getTreeOrderIndex());
        }
        sql.append(" FROM " + tableName + " t ");
        sql.append(" where 1=1 ");
        if (null != queryInfo) {
            sql.append(queryInfo.getWhereSql());
        }
        if (!onlyWhereSql) {
            if (isRoot) {
                sql.append(" AND (t." + template.getParent() + "='" + rootId + "' OR t." + template.getId() + "='" + rootId + "')");
            } else {
                sql.append("AND t." + template.getParent() + "='" + rootId + "'");
            }
        }
        if (null != queryInfo && !Strings.isNullOrEmpty(queryInfo.getOrderSql())) {
            sql.append(queryInfo.getOrderSql());
        } else {
            sql.append(" ORDER BY " + template.getParent() + " asc");
            if (!Strings.isNullOrEmpty(template.getOrderIndex())) {
                sql.append(", " + template.getOrderIndex() + " asc ");
            }
        }
        return sql.toString();
    }

    /**
     * 查询树形的count
     *
     * @param template  TODO 暂不明确
     * @param queryInfo TODO 暂不明确
     * @param tableName 表名称
     * @param rootId    根节点id
     * @return
     */
    @Override
    public String getAsynTreeCount(JSONTreeNode template, QueryInfo queryInfo,
                                   String tableName, String rootId) {
        // TODO Auto-generated method stub
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT count(t." + template.getId()); // 0
        sql.append(") FROM " + tableName + " t ");
        sql.append(" where 1=1 ");
        if (null != queryInfo) {
            sql.append(queryInfo.getWhereSql());
        }
        if (!Strings.isNullOrEmpty(rootId)) {
            sql.append(" and t." + template.getParent() + " = '" + rootId + "'");
        } else {
            sql.append(" and t." + template.getParent() + " is null ");
        }
        return sql.toString();
    }

    /**
     * 构建DynaBean查询同步树的sql语句
     *
     * @param columns
     * @param template  TODO 暂不明确
     * @param tableName 表名称
     * @param rootId    根节点id
     * @param queryInfo TODO 暂不明确
     * @return
     */
    @Override
    public String getDynaTreeSql(List<DynaBean> columns, JSONTreeNode template, String tableName, String rootId, QueryInfo queryInfo) {
        // TODO Auto-generated method stub
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ");
        for (DynaBean column : columns) {
            sql.append(" t." + column.getStr("TABLECOLUMN_CODE") + ",");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(" FROM " + tableName + " t ");
        sql.append(" where (1=1 ");
        if (null != queryInfo) {
            sql.append(queryInfo.getWhereSql());
        }
        sql.append(") OR " + template.getId() + " = '" + rootId + "' ");
        sql.append(" START WITH t." + template.getId() + " = '" + rootId + "'");
        sql.append(" CONNECT BY t." + template.getParent() + " = PRIOR " + template.getId() + " ");
        if (null != queryInfo && !Strings.isNullOrEmpty(queryInfo.getOrderSql())) {
            sql.append(queryInfo.getOrderSql());
        } else {
            sql.append(" ORDER BY " + template.getParent() + " ASC, SY_ORDERINDEX ASC");
        }
        return sql.toString();
    }

    /**
     * 获取角色权限查询sql
     *
     * @param rootId 根节点
     * @return
     */
    @Override
    public String getRolePermSql(String rootId) {
        // TODO Auto-generated method stub
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT t.ROLEID"); // 0
        sql.append(", t.ROLENAME"); // 1
        sql.append(", t.ROLECODE"); // 2
        sql.append(", t.GROUPNAME"); // 3
        sql.append(", t.GROUPCODE"); // 4
        sql.append(", t.EXTENDGROUPNAME"); // 5
        sql.append(", t.EXTENDGROUPCODE"); // 6
        sql.append(", t.REJECTGROUPNAME"); // 7
        sql.append(", t.REJECTGROUPCODE"); // 8
        sql.append(", t.PATH"); // 9
        sql.append(", t.PARENT"); // 10
        sql.append(" FROM JE_CORE_ROLE t ");
        sql.append(" where 1=1 ");
        sql.append(" START WITH t.ROLEID = '" + rootId + "'");
        sql.append(" CONNECT BY t.parent = PRIOR ROLEID");
        sql.append(" ORDER BY PARENT asc, ORDERINDEX asc");
        return sql.toString();
    }

    /**
     * 获取角色权限查询sql
     *
     * @param rootId 根节点
     * @return
     */
    @Override
    public String getRolePermLikeSql(String rootId) {
        // TODO Auto-generated method stub
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT t.ROLEID"); // 0
        sql.append(", t.ROLENAME"); // 1
        sql.append(", t.ROLECODE"); // 2
        sql.append(", t.GROUPNAME"); // 3
        sql.append(", t.GROUPCODE"); // 4
        sql.append(", t.EXTENDGROUPNAME"); // 5
        sql.append(", t.EXTENDGROUPCODE"); // 6
        sql.append(", t.REJECTGROUPNAME"); // 7
        sql.append(", t.REJECTGROUPCODE"); // 8
        sql.append(", t.PATH"); // 9
        sql.append(", t.PARENT"); // 10
        sql.append(" FROM JE_CORE_ROLE t ");
        sql.append(" where 1=1 ");
        sql.append(" AND PATH LIKE '%" + rootId + "%'");
//		sql.append(" CONNECT BY t.parent = PRIOR ROLEID");
        sql.append(" ORDER BY PARENT asc, ORDERINDEX asc");
        return sql.toString();
    }

    /**
     * 获取角色组查询SQL
     *
     * @param rootId 根节点
     * @return
     */
    @Override
    public String getRoleGroupPermSql(String rootId) {
        // TODO Auto-generated method stub
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT t.JE_CORE_ROLEGROUP_ID"); // 0
        sql.append(", t.ROLEGROUP_TEXT"); // 1
        sql.append(", t.ROLEGROUP_CODE"); // 2
        sql.append(", t.SY_PATH"); // 9
        sql.append(", t.SY_PARENT"); // 10
        sql.append(" FROM JE_CORE_ROLEGROUP t ");
        sql.append(" where 1=1 ");
        sql.append(" START WITH t.JE_CORE_ROLEGROUP_ID = '" + rootId + "'");
        sql.append(" CONNECT BY t.SY_PARENT = PRIOR JE_CORE_ROLEGROUP_ID");
        sql.append(" ORDER BY SY_PARENT asc, SY_ORDERINDEX asc");
        return sql.toString();
    }

    /**
     * 获取角色组查询SQL
     *
     * @param rootId 根节点
     * @return
     */
    @Override
    public String getRoleGroupPermLikeSql(String rootId) {
        // TODO Auto-generated method stub

        StringBuffer sql = new StringBuffer();
        sql.append("SELECT t.JE_CORE_ROLEGROUP_ID"); // 0
        sql.append(", t.ROLEGROUP_TEXT"); // 1
        sql.append(", t.ROLEGROUP_CODE"); // 2
        sql.append(", t.SY_PATH"); // 9
        sql.append(", t.SY_PARENT"); // 10
        sql.append(" FROM JE_CORE_ROLEGROUP t ");
        sql.append(" where 1=1 ");
        sql.append(" AND SY_PATH LIKE '%" + rootId + "%'");
//		sql.append(" CONNECT BY t.SY_PARENT = PRIOR JE_CORE_ROLEGROUP_ID");
        sql.append(" ORDER BY SY_PARENT asc, SY_ORDERINDEX asc");
        return sql.toString();
    }

    /**
     * 获取数据生成UUID函数
     *
     * @return
     */
    @Override
    public String getGenerateUUID() {
        // TODO Auto-generated method stub
        return "sys_guid()";
    }

    /**
     * 获取截取字符串函数
     *
     * @return
     */
    @Override
    public String getSubString() {
        // TODO Auto-generated method stub
        return "substr";
    }

    /**
     * 获取字符串长度函数
     *
     * @return
     */
    @Override
    public String getLength() {
        // TODO Auto-generated method stub
        return "length";
    }

    /**
     * 获取数据库修改视图语句
     *
     * @return
     */
    @Override
    public String getUpdateView() {
        // TODO Auto-generated method stub
        return "CREATE OR REPLACE VIEW ";
    }

}
